package cn.itlaobing.dao;

import cn.itlaobing.model.PanationVir;
import cn.itlaobing.model.VIRDto;
import cn.itlaobing.model.VegetableImportRecordModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2017-11-10.
 */
public class VIRDao extends BaseDao {
    public PanationVir QueryAll(Integer index, Integer size) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        PanationVir<VIRDto> panationVir=new PanationVir<>();
        String sql="SELECT vir.`id` id,vt.`name` as dtoName,vir.`quantity` quantity FROM vegetableImportRecord vir INNER JOIN vegetabletypes vt ON vir.`typeId`=vt.`id` order by id limit ?,?";
        BeanListHandler<VIRDto> beanHandler=new BeanListHandler<VIRDto>(VIRDto.class);
        List<VIRDto> list=qr.query(sql,beanHandler,(index-1)*size,size);
        String sql1="SELECT COUNT(1) FROM vegetableimportrecord";

        Long r=qr.query(sql1,new ScalarHandler<Long>());
        int rr=r.intValue();
        panationVir.setIndex(index);
        panationVir.setList(list);
        panationVir.setSize(size);
        panationVir.setTotal(rr);
        System.out.println(list.toString());
        return panationVir;
    }
    public int delete(String id) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="DELETE FROM vegetableImportRecord WHERE id=?";
        BeanHandler<VegetableImportRecordModel> beanHandler=new BeanHandler<VegetableImportRecordModel>(VegetableImportRecordModel.class);
        int r=qr.update(sql,id);
        return r;

    }
    public int update(VegetableImportRecordModel virm) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="UPDATE vegetableImportRecord SET typeId=?,quantity=? WHERE id=?";
        BeanHandler<VegetableImportRecordModel> beanHandler=new BeanHandler<VegetableImportRecordModel>(VegetableImportRecordModel.class);
        int r=qr.update(sql,virm.getTypeId(),virm.getQuantity(),virm.getId());
        return r;
    }
    public VegetableImportRecordModel updateBefore(String id) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource())  ;
        String sql="select * from vegetableImportRecord where id=?";
        BeanHandler<VegetableImportRecordModel> beanHandler=new BeanHandler<VegetableImportRecordModel>(VegetableImportRecordModel.class);
        VegetableImportRecordModel vegetableImportRecordModel=qr.query(sql,beanHandler,id);
        return vegetableImportRecordModel;
    }
}
